package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc6ChequeBean;
import gov.nhso.prov.acc.beans.Acc6ChequeDetailBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.UserInfo;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60311 extends Utility{
	public String url = "/apps/acc6/ACC60311A.jsp";
	
	public ACC60311(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		//System.out.println("job >> " + job);
		if (job.equals("init")) {
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		} else if (job.equals("search")) {
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("view")) {
			this.view(request);
		} else if (job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.clearLastRequest(request, "lastSearch");
				this.init(request);
			}
		} else if (job.equals("cancel")){
			this.cancel(request);
		} else if (job.equals("confirm")){
			this.confirm(request);
		}
	}
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("chequeStatusSel", "A");
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ChequeBean acb = new Acc6ChequeBean();
			String provinceId = this.getAttr(request, "provinceId");
			String docNo = this.getAttr(request, "docNo");
			String refNo = this.getAttr(request, "refNo");
			String chequeNo = this.getAttr(request, "chequeNo");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String chequeStatus = this.getAttr(request, "chequeStatus");
			String condition = "";
			this.beanUtil().copyRequest2Bean(request, pageBean);

			acb.setProvinceId(provinceId);
			acb.setDocNo(docNo);
			acb.setChequeNo(chequeNo);
			acb.setActive("Y");
			acb.setChequeStatus(chequeStatus);
			
			if (startDate != null && !startDate.equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(CHEQUE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}
			if (endDate != null && !endDate.equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(CHEQUE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}

			if (refNo != null && !refNo.equals("")) {
				condition += " AND DOC_NO IN (SELECT DOC_NO FROM ACC6_CHEQUE_DETAIL WHERE REF_NO LIKE '%" + refNo + "%') ";
			}
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(acb, " " + condition,pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("startDate", startDate);
			request.setAttribute("endDate", endDate);
			request.setAttribute("docNo", docNo);
			request.setAttribute("chequeNo", chequeNo);
			request.setAttribute("job", "search");
			request.setAttribute("chequeStatusSel", chequeStatus);
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("areaLevel", this.user(request).getAreaLevel());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60311A.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6ChequeBean acb = new Acc6ChequeBean();
			Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
			List lsChequeDetail = new ArrayList();
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			acb.setDocNo(docNo);
			acb.setProvinceId(provinceId);
			dao.select(acb);
			acdb.setDocNo(docNo);
			acdb.setActive("Y");
			lsChequeDetail = dao.selectList(acdb);
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			lsChequeDetail = dao.selectList(acdb, xcols," ORDER BY CHEQUE_SEQ ASC");
			for (int i = 0; i < lsChequeDetail.size(); i++) {
				Acc6ChequeDetailBean bean = (Acc6ChequeDetailBean) lsChequeDetail.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				} else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if(pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				} else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				} else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if(pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				} else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
			}
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			List listCategory = new ArrayList();
			listCategory = dao.selectList(expenseCategoryBean);
			
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("acb", acb);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("lsChequeDetail", lsChequeDetail);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("job", "confirm");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				url = "/apps/acc6/ACC60311B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String cancel(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6ChequeBean acb = new Acc6ChequeBean();
			this.beanUtil().copyRequest2Bean(request, acb);
			acb.setChequeStatus("P");
			acb.setChequeDate(this.dateUtil().changeDateToDB(acb.getChequeDate()));
			acb.setUpdatedBy(this.user(request).getUserId());
			acb.setUpdatedDate(this.sysDate);
			dao.update(acb);
			this.message(request).setMsg("I", "บันทึกข้อมูลเสร็จสิ้น");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("chequeStatusSel", "A");
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String confirm(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		Acc6ChequeBean acb = new Acc6ChequeBean();
		Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
		Acc6TransactionBean transactionBean = new Acc6TransactionBean();
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String ts = "";
			this.beanUtil().copyRequest2Bean(request, acb);
			String chequeDate = acb.getChequeDate();
			UserInfo ui = this.user(request);
			
			Acc6ChequeBean bean = new Acc6ChequeBean();
			bean.setDocNo(acb.getDocNo());
			bean.setProvinceId(acb.getProvinceId());
			bean.setChequeNo(acb.getChequeNo());
			dao.select(bean);
			if (bean.getChequeStatus().equals("A")) {
				String[] chequeSeq = request.getParameterValues("chequeSeq");
				String[] refNo = request.getParameterValues("refNo");
				String[] budgetYear = request.getParameterValues("budgetYear");
				String[] allocateCode = request.getParameterValues("allocateCode");
				String[] venderCode = request.getParameterValues("venderCode");
				String[] categoryId = request.getParameterValues("categoryId");
				String[] chequeAmount = request.getParameterValues("chequeAmount");
				String[] taxValue = request.getParameterValues("taxValue");
				String[] isBorrow = request.getParameterValues("isBorrow");
				chequeDate = this.dateUtil().changeDateToDB(chequeDate);
				for(int i =0;i<refNo.length;i++){
					ts = this.dateUtil().getCurrentTimeStamp2DB();
					transactionBean = new Acc6TransactionBean();
					acdb = new Acc6ChequeDetailBean();
					
					transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					transactionBean.setTransactionType("C");
					transactionBean.setBudgetYear(budgetYear[i]);
					transactionBean.setProvinceId(acb.getProvinceId());
					transactionBean.setExpenseDate(chequeDate);
					transactionBean.setChequeDate(chequeDate);
					transactionBean.setRefNo(this.encodeUTF(refNo[i]));
					transactionBean.setCategoryId(categoryId[i]);
					transactionBean.setVenderCode(venderCode[i]);
					transactionBean.setAllocateCode(allocateCode[i]);
					transactionBean.setExpenseAmount(chequeAmount[i]);
					transactionBean.setTaxValue(taxValue[i]);
					transactionBean.setIsBorrow(isBorrow[i]);
					transactionBean.setChequeNo(acb.getChequeNo());
					dao.insert(transactionBean);
					
					acdb.setDocNo(acb.getDocNo());
					acdb.setChequeSeq(chequeSeq[i]);
					acdb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					acdb.setUpdatedBy(ui.getUserId());
					acdb.setUpdatedDate(this.sysDate);
					dao.update(acdb);
				}
				acb.setChequeDate(this.dateUtil().changeDateToDB(acb.getChequeDate()));
				acb.setChequeStatus("W");
				dao.update(acb);
				
				this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			} else {
				this.message(request).setMsg("E", "ไม่สามารถยืนยันได้ สถานะการจ่ายไม่เป็นรออนุมัติ กรุณาตรวจสอบ");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				request.setAttribute("docNo", acb.getDocNo());
				request.setAttribute("provinceId", acb.getProvinceId());
				this.view(request);
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
